/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

/**
 *
 * @author sheked
 */
public class CarretDB {
    private QueryRunner run;
    private MapListHandler mlh;
    private MapHandler mh;

    public CarretDB() throws NamingException, SQLException {
            Context ctx = new InitialContext();
            DataSource odsconn = (DataSource)ctx.lookup("jdbc/volansbd");
            run = new QueryRunner(odsconn);
            mlh = new MapListHandler();
            mh = new MapHandler();
    }

    public int introduir(int idTicket, int idProd, int unitats) throws SQLException {
        try {
            int existencies = ((BigDecimal)run.query("select stock from productes where id_prod=?", mh, idProd).get("stock")).intValue();
            if (existencies < unitats) {
                return 1;
            }
        } catch (NullPointerException e) {
            return 1;
        }

        Map<String, Object> select = run.query("select * from lin_tickets where id_ticket=? and id_prod=?", mh, idTicket,idProd);
        String consulta = "INSERT INTO \"IG21254\".\"LIN_TICKETS\" (quantitat,id_ticket,id_prod) VALUES (?,?,?)";
        if (select != null && !select.isEmpty()) {
            consulta = "UPDATE lin_tickets SET quantitat=? WHERE id_ticket=? and id_prod=?";
        }
        int num = run.update(consulta, unitats, idTicket, idProd);
        if (num == 0) {
            return 2;
        }

        if (select == null || select.isEmpty()) {
            int num2 = run.update("update ticket set items=((select items from ticket where id_ticket=?)+1) where id_ticket=?", idTicket, idTicket);
            if (num2 == 0) {
                return 3;
            }
        }
        return 0;
    }

    public int crear(int idClient) throws SQLException {
        try {
            int idticket =  ((BigDecimal) run.query("SELECT idticket.NEXTVAL FROM dual", mh).get("NEXTVAL")).intValue();
            int num = run.update("INSERT INTO \"IG21254\".\"TICKET\" (id_ticket,data,id_client) VALUES (?,current_date,?)", idticket, idClient);
            if (num != 0) {
                return idticket;
            }
            return -1;
        } catch(NullPointerException e) {
            return -1;
        }
    }

    public int crear() throws SQLException {
        try {
            int idticket = ((BigDecimal) run.query("SELECT idticket.NEXTVAL FROM dual", mh).get("NEXTVAL")).intValue();
            int num = run.update("INSERT INTO \"IG21254\".\"TICKET\" (id_ticket,data) VALUES (?,current_date)", idticket);
            if (num != 0) {
                return idticket;
            }
            return -1;
        } catch(NullPointerException e) {
            return -1;
        }
    }

    public boolean update(int idTicket, int idClient) throws SQLException {
        int num = run.update("UPDATE ticket SET id_client=? WHERE id_ticket=?", idClient, idTicket);
        if (num == 0) {
            return false;
        }
        return true;
    }

    public float preuTotal(int id) throws SQLException {
        List<Map<String,Object>> llista = run.query("select productes.preu,lin_tickets.quantitat from productes,lin_tickets where productes.id_prod=lin_tickets.id_prod and id_ticket=?", mlh, id);
        float suma = 0;
        Iterator<Map<String, Object>> it = llista.iterator();
        Map<String, Object> item = null;
        while (it.hasNext()) {
            item = it.next();
            suma += (((BigDecimal)item.get("preu")).floatValue())*(((BigDecimal)item.get("quantitat")).intValue());
        }
        return suma;
    }

    public int itemsTotal(int id) throws SQLException {
        try {
            return ((BigDecimal) run.query("select items from ticket where id_ticket=?", mh, id).get("items")).intValue();
        } catch (NullPointerException e) {
            return -1;
        }
    }

    public List<Map<String, Object>> llistat(int id) throws SQLException {
        return run.query("select productes.nom,productes.preu,lin_tickets.quantitat ,lin_tickets.id_prod from lin_tickets,productes where id_ticket=? and productes.id_prod=lin_tickets.id_prod", mlh, id);
    }

    public boolean eliminarItem(int idTicket, int idProd) throws SQLException {
        int num = run.update("DELETE FROM lin_tickets WHERE id_prod=? and id_ticket=?", idProd, idTicket);
        if (num == 0) {
            return false;
        }

        num = run.update("update ticket set items=((select items from ticket where id_ticket=?)-1) where id_ticket=?", idTicket, idTicket);
        if (num == 0) {
            return false;
        }
        return true;
    }

    public boolean buidar(int idTicket) throws SQLException {
        int num = run.update("DELETE FROM lin_tickets WHERE id_ticket=?", idTicket);
        if (num == 0) {
            return false;
        }

        num = run.update("update ticket set items=0 where id_ticket=?", idTicket);
        if (num == 0) {
            return false;
        }
        return true;
    }

    public int pagar(int idTicket) throws SQLException {
        int num = run.update("update ticket set pend_fact=0, data_p=current_date where id_ticket=?", idTicket);
        if (num == 0) {
            return 1;
        }
        num = run.update("UPDATE productes SET stock=stock-(SELECT quantitat FROM lin_tickets WHERE lin_tickets.id_prod=productes.id_prod and id_ticket=?) WHERE exists (SELECT id_prod FROM lin_tickets WHERE lin_tickets.id_prod=productes.id_prod and id_ticket=?)"
                        , idTicket, idTicket);
        if (num == 0) {
            return 2;
        }
        return 0;
    }

    public List<Map<String, Object>> carretsAnteriors(int idClient) throws SQLException {
        return run.query("select * from ticket where id_client=? order by data desc", mlh, idClient);
    }
}
